package mwa.cs.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Excel {

	@SuppressWarnings({ "rawtypes" })
	public ArrayList<HashMap> readXLSFile(String pathFile) throws IOException {
		ArrayList<HashMap> voDataList = new ArrayList<HashMap>();
		HashMap<Object, Object> voMap = new HashMap<Object, Object>();
		InputStream voExcelFileToRead = new FileInputStream(pathFile); // "C:\\java\\myExcel.xls"
		HSSFWorkbook voWb = new HSSFWorkbook(voExcelFileToRead);
		HSSFSheet voSheet = voWb.getSheetAt(0);
		HSSFRow voRow;
		Iterator<Row> rows = voSheet.rowIterator();
		int i = 0;
		while (rows.hasNext()) {
			i++;
			voRow = (HSSFRow) rows.next();
			
			System.out.print("บรรทัดที่ " + i + "\t" );
			
			voMap = new HashMap<Object, Object>();
			
			for(int j=0;j<voRow.getLastCellNum();j++){
				
				if(voRow.getCell(j)==null){
					voMap.put(j, "");
					System.out.print(voMap.get(j) + "\t");
				}else{
					voMap.put(j, voRow.getCell(j));
					System.out.print(voMap.get(j) + "\t");
				}
				
				
			}
			
			voDataList.add(voMap);
			
			System.out.println();
		}
		
		return voDataList;

	}

	@SuppressWarnings("rawtypes")
	public ArrayList<HashMap> readXLSXFile(String pathFile) throws IOException {
		ArrayList<HashMap> voDataList = new ArrayList<HashMap>();
		HashMap<Object, Object> voMap = new HashMap<Object, Object>();
		InputStream voExcelFileToRead = new FileInputStream(pathFile); // "C:\\java\\myExcel.xlsx"

		XSSFWorkbook voWb = new XSSFWorkbook(voExcelFileToRead);

		XSSFSheet voSheet = voWb.getSheetAt(0);
		XSSFRow voRow;
		XSSFCell voCell;

		Iterator<Row> rows = voSheet.rowIterator();
		System.out.println("Start : " + new Date().getTime());
		while (rows.hasNext()) {
			voRow = (XSSFRow) rows.next();
			Iterator<org.apache.poi.ss.usermodel.Cell> cells = voRow
					.cellIterator();
			
			voMap = new HashMap<Object, Object>();
			for(int j=0;j<voRow.getLastCellNum();j++){
				
				if(voRow.getCell(j)==null){
					voMap.put(j, "");
					System.out.print(voMap.get(j) + "\t");
				}else{
					voMap.put(j, voRow.getCell(j));
					System.out.print(voMap.get(j) + "\t");
				}
				
				
			}
			
			voDataList.add(voMap);
			
			System.out.println();

		}
		System.out.println("End : " + new Date().getTime());
		System.out.println(System.currentTimeMillis());
		return voDataList;

	}

	@SuppressWarnings({ "rawtypes" })
	public void writeXLSFile(ArrayList<HashMap> dataList, String excelFileName,
			String sheetName) throws IOException {

		HSSFWorkbook voWb = new HSSFWorkbook();
		HSSFSheet voSheet = voWb.createSheet(sheetName);

		// iterating r number of rows
		for (int r = 0; r < dataList.size(); r++) {
			HSSFRow voRow = voSheet.createRow(r);
			// iterating c number of columns
			for (int c = 0; c < dataList.get(r).size(); c++) {
				HSSFCell voCell = voRow.createCell(c);
				voCell.setCellValue("" + dataList.get(r).get(c));
			}
		}

		FileOutputStream voFileOut = new FileOutputStream(excelFileName);

		// write this workbook to an Outputstream.
		voWb.write(voFileOut);
		voFileOut.flush();
		voFileOut.close();
	}

	@SuppressWarnings("rawtypes")
	public void writeXLSXFile(ArrayList<HashMap> dataList,
			String excelFileName, String sheetName) throws IOException {

		XSSFWorkbook voWb = new XSSFWorkbook();
		XSSFSheet voSheet = voWb.createSheet(sheetName);

		// iterating r number of rows
		for (int r = 0; r < dataList.size(); r++) {
			XSSFRow voRow = voSheet.createRow(r);
			// iterating c number of columns
			for (int c = 0; c < dataList.get(r).size(); c++) {
				XSSFCell cell = voRow.createCell(c);
				cell.setCellValue("" + dataList.get(r).get(c));
			}
		}

		FileOutputStream voFileOut = new FileOutputStream(excelFileName);

		// write this workbook to an Outputstream.
		voWb.write(voFileOut);
		voFileOut.flush();
		voFileOut.close();
	}
}
